package com.zhx.taoda.utils;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.util.IOUtils;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.*;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.List;

public class ExcelUtils {

    /**
     * 获取excel数据
     * @param filePath 文件路径
     * @return
     */
    public static List<List<String>> getExcelData(String filePath) {
        List<List<String>> list = new ArrayList<>();
        try {
            ExcelUtils utils = new ExcelUtils(new File(filePath));
            list = utils.read();
        } catch (Exception e) {
            e.printStackTrace();
        }
        return list;
    }

    private Workbook workbook;

    public ExcelUtils(Workbook workbook) {
        this.workbook = workbook;
    }

    public ExcelUtils(File file) throws IOException {
        FileInputStream is = null;
        try {
            is = new FileInputStream(file);
            if (file.getName().endsWith(".xls")) {
                // Excel2003及以前版本
                workbook = new HSSFWorkbook(is);
            } else {
                // Excel2007及以后版本
                workbook = new XSSFWorkbook(is);
            }
        } finally {
            IOUtils.closeQuietly(is);
        }
    }


    /**
     * 读取Excel第一页所有数据
     *
     * @return
     * @throws Exception
     */
    public List<List<String>> read() throws Exception {
        return read(0, 0, getRowCount(0));
    }


    /**
     * 获取指定sheet页行数
     *
     * @param sheetIx
     * @return
     */
    private Integer getRowCount(int sheetIx) {
        Sheet sheet = workbook.getSheetAt(sheetIx);
        if (sheet.getPhysicalNumberOfRows() == 0) {
            return 0;
        }
        return sheet.getLastRowNum() + 1;
    }

    /**
     * 读取指定sheet页指定行数据
     *
     * @param sheetIx 指定sheet页，从0开始
     * @param start   指定开始行，从0开始
     * @param end     指定结束行，从0开始
     * @return
     * @throws Exception
     */
    private List<List<String>> read(int sheetIx, int start, int end) throws Exception {
        Sheet sheet = workbook.getSheetAt(sheetIx);
        List<List<String>> rowList = new ArrayList<>();

        if (end > getRowCount(sheetIx)) {
            end = getRowCount(sheetIx);
        }
        // 第一行总列数
        int colNum = sheet.getRow(0).getLastCellNum();
        for (int i = start; i < end; i++) {
            List<String> colList = new ArrayList<>();
            Row row = sheet.getRow(i);
            for (int j = 0; j < colNum; j++) {
                if (row == null) {
                    colList.add(null);
                    continue;
                }
                colList.add(getCellValue(row.getCell(j)));
            }
            rowList.add(colList);
        }
        return rowList;
    }

    /**
     * 单元格内容解析
     *
     * @param cell
     * @return
     */
    private String getCellValue(Cell cell) {
        String cellValue = null;
        switch (cell.getCellTypeEnum()) {
            // 数字
            case NUMERIC:
                if (DateUtil.isCellDateFormatted(cell)) {
                    SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
                    cellValue = sdf.format(DateUtil.getJavaDate(cell.getNumericCellValue()));
                } else {
                    DataFormatter dataFormatter = new DataFormatter();
                    cellValue = dataFormatter.formatCellValue(cell);
                }
                break;
            // 字符串
            case STRING:
                cellValue = cell.getStringCellValue();
                break;
            // Boolean
            case BOOLEAN:
                cellValue = String.valueOf(cell.getBooleanCellValue());
                break;
            // 公式
            case FORMULA:
                cellValue = cell.getCellFormula();
                break;
            // 空值
            case BLANK:
                cellValue = null;
                break;
            // 错误
            case ERROR:
                cellValue = "非法字符";
                break;
            default:
                cellValue = "未知类型";
                break;
        }
        return cellValue;
    }
}
